﻿using System.Data;
using Npgsql;
using NpgsqlTypes;
using PureMVC.Interfaces;
using PureMVC.Patterns;
using SMS.Core.Model.Data;

namespace SMS.Core.Model.Customer
{
    public class CustomerProxy : Proxy, IProxy
    {
        #region Constructors

        public CustomerProxy(NpgsqlConnection conn)
        {
            m_conn = conn;
            m_dataAdapter = new NpgsqlDataAdapter(QRY_ADAPTER_SELECT, conn);
            m_dataAdapter.Fill(AppDataCache.Instance.Data.customer);
            InitializeDataAdapter();
            InitializeEventHandlers();
        }

        #endregion

        #region Constants

        #region Queries
        /// <summary>
        /// Returns all the rows and columns of the customer table.
        /// </summary>
        private const string QRY_ADAPTER_SELECT = 
            @"SELECT * FROM customer;";

        /// <summary>
        /// The <c>NpgsqlDataAdapter</c> insert query for the <c>customer</c> table.
        /// </summary>
        private const string QRY_ADAPTER_INSERT = // HACK: Need to handle case where cust_id is already taken.
            @"INSERT INTO customer (cust_id, first_name, last_name, credit_limit, address)
              VALUES (@cust_id, @first_name, @last_name, @credit_limit, @address);";

        /// <summary>
        /// The <c>NpgsqlDataAdapter</c> update query for the <c>customer</c> table.
        /// </summary>
        private const string QRY_ADAPTER_UPDATE =
            @"UPDATE customer
              SET    first_name = @first_name,
                     last_name = @last_name,
                     credit_limit = @credit_limit,
                     address = @address
              WHERE  cust_id = @cust_id;";

        private const string QRY_ADAPTER_DELETE =
            @"DELETE FROM customer
              WHERE  cust_id = @cust_id;";

        #endregion

        #endregion

        #region Public Methods
        /// <summary>
        /// Adds one customer to the <c>customerDataTable</c> and commits changes to the database.
        /// </summary>
        /// <param name="transaction">The database transaction handling the update.</param>
        /// <remarks>Precondition: This method assumes that the changes to the <c>customerDataTable</c>
        /// instance is already validated.</remarks>
        /// <returns>The number of rows updated.</returns>
        public int UpdateCustomerTable(NpgsqlTransaction transaction)
        {
            m_dataAdapter.UpdateCommand.Transaction = transaction;
            m_dataAdapter.InsertCommand.Transaction = transaction;
            m_dataAdapter.DeleteCommand.Transaction = transaction;
            // TODO: Exception handling for UpdateCustomerTable()
            return m_dataAdapter.Update(AppDataCache.Instance.Data.customer);
        }

        #endregion

        #region Non-Public Methods

        private void InitializeDataAdapter()
        {
            // TODO: Work on InitializeDataAdapter()
            var insCmd = new NpgsqlCommand(QRY_ADAPTER_INSERT, m_conn);
            insCmd.Parameters.Add("cust_id", NpgsqlDbType.Integer, 10, "cust_id");
            insCmd.Parameters.Add("first_name", NpgsqlDbType.Varchar, 50, "first_name");
            insCmd.Parameters.Add("last_name", NpgsqlDbType.Varchar, 50, "last_name");
            insCmd.Parameters.Add("credit_limit", NpgsqlDbType.Numeric, 9, "credit_limit");
            insCmd.Parameters.Add("address", NpgsqlDbType.Varchar, 80, "address");

            m_dataAdapter.InsertCommand = insCmd;

            var updCmd = new NpgsqlCommand(QRY_ADAPTER_UPDATE, m_conn);
            updCmd.Parameters.Add("first_name", NpgsqlDbType.Varchar, 50, "first_name");
            updCmd.Parameters.Add("last_name", NpgsqlDbType.Varchar, 50, "last_name");
            updCmd.Parameters.Add("credit_limit", NpgsqlDbType.Numeric, 9, "credit_limit");
            updCmd.Parameters.Add("address", NpgsqlDbType.Varchar, 80, "address");
            updCmd.Parameters.Add("cust_id", NpgsqlDbType.Integer, 10, "cust_id");

            m_dataAdapter.UpdateCommand = updCmd;

            var delCmd = new NpgsqlCommand(QRY_ADAPTER_DELETE, m_conn);
            delCmd.Parameters.Add("cust_id", NpgsqlDbType.Integer, 10, "cust_id");

            m_dataAdapter.DeleteCommand = delCmd;

            m_dataAdapter.TableMappings.Add("customer", "customer");
        }

        private void InitializeEventHandlers()
        {
            
        }

        private void ListenForNotifications()
        {
            if (m_conn.State != ConnectionState.Open)
                m_conn.Open();
            NpgsqlCommand listenCmd = 
                new NpgsqlCommand("LISTEN updated_customer;", m_conn);
            listenCmd.ExecuteNonQuery();
            m_conn.Close();
        }

        #endregion

        #region Members

        private NpgsqlConnection m_conn;
        
        #region Static Members

        private static object m_staticSyncRoot = new object();
        private static NpgsqlDataAdapter m_dataAdapter;

        #endregion

        #region Properties

        public new AppData.customerDataTable Data
        {
            get { return AppDataCache.Instance.Data.customer; }
        }

        #endregion

        #endregion
    }
}
